﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Collections;

namespace INSAForManagers
{
    class DBUtil
    {
        private static OleDbConnection oleCon = new OleDbConnection();
        private static OleDbTransaction oleTrans;

        internal static void oleConnect()
        {
            try
            {
                //MDB와 연결
                DBUtil.oleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Config.Path + "\\INSA.mdb;Persist Security Info=False;Jet OLEDB:Database Password=heback1972";                
                DBUtil.oleCon.Open();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

        internal static void beginTans()
        {
            oleTrans = oleCon.BeginTransaction();
        }

        internal static void rollBack()
        {
            oleTrans.Rollback();
        }

        internal static void commit()
        {
            oleTrans.Commit();
        }

        internal static void oleClose()
        {
            //현재 MDB와 연결되어 있으면 연결을 해제한다.
            if (DBUtil.oleCon.State != ConnectionState.Closed)
            {
                DBUtil.oleCon.Close();
            }
        }

        //패스워드 확인(inx)
        internal static bool checkPassword(int inx, string p)
        {
            OleDbCommand cmd = null;
            string password = "";
            try
            {
                cmd = new OleDbCommand("SELECT USER_PW FROM USERS WHERE INX=" + inx, DBUtil.oleCon);
                password = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            if (password.Equals(p))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //비밀번호 가져오기
        internal static string getPasseord()
        {
            OleDbCommand cmd = null;
            string password = "";
            try
            {
                cmd = new OleDbCommand("SELECT USER_PW FROM USERS", DBUtil.oleCon);
                password = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return password;
        }

        //과목 그룹 목록 불러오기
        internal static ArrayList getSubjectGroupList()
        {
            ArrayList list = new ArrayList();
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            try
            {
                if (DBUtil.oleCon.State == ConnectionState.Open)
                {

                    cmd = new OleDbCommand("SELECT SUBJECT_GROUP FROM SUBJECTINFO GROUP BY SUBJECT_GROUP", DBUtil.oleCon);
                    read = cmd.ExecuteReader();

                    while (read.Read())
                    {
                        list.Add(read["SUBJECT_GROUP"]);
                    }

                }
                else
                {
                    throw new Exception("MDB연결이 끊겼습니다.   ");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }           

            if (read.IsClosed == false)
            {
                read.Close();
            }

            return list;
        }

        //지정된 과목 그룹에 대한 과목 불러오기
        internal static ArrayList getSubject(string subject_group)
        {
            ArrayList list = new ArrayList();
            OleDbCommand cmd = null;
            OleDbDataReader read = null; 
            
            try
            {

                cmd = new OleDbCommand("SELECT SUBJECT_NAME FROM SUBJECTINFO WHERE SUBJECT_GROUP='" + subject_group + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                
                while (read.Read())
                {
                    list.Add(read["SUBJECT_NAME"]);
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            if (read.IsClosed == false)
            {
                read.Close();
            }

            return list;
        }

        //과목 목록 불러오기
        internal static ArrayList getSubjectList()
        {
            ArrayList list = new ArrayList();
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            try
            {

                cmd = new OleDbCommand("SELECT SUBJECT_NAME FROM SUBJECTINFO ORDER BY SUBJECT_NAME", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    list.Add(read["SUBJECT_NAME"]);
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            if (read.IsClosed == false)
            {
                read.Close();
            }

            return list;
        }

        //특정 과목의 과목 그룹 찾기
        internal static string getSubjectGroup(string subject)
        {

            string subject_group = null;
            OleDbCommand cmd = null;

            try
            {

                cmd = new OleDbCommand("SELECT SUBJECT_GROUP FROM SUBJECTINFO WHERE SUBJECT_NAME='"+subject+"'", DBUtil.oleCon);                
                subject_group = ((string)cmd.ExecuteScalar()).Length>0?(string)cmd.ExecuteScalar():"";

            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                cmd.Dispose();
            }

            return subject_group;

        }

        //소속 기관 검색
        internal static ArrayList getOrganList(string organ)
        {
            ArrayList list = new ArrayList();
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            try
            {

                cmd = new OleDbCommand("SELECT NAME FROM ORGANTABLE WHERE NAME like '%" + organ + "%'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    list.Add(read["NAME"]);
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            if (read.IsClosed == false)
            {
                read.Close();
            }

            return list;
        }

        //소속 기관명 -> 소속 기관 코드
        internal static string getOrganCode(string organ_name) 
        {
            OleDbCommand cmd = null;

            try
            {
                cmd = new OleDbCommand("SELECT CODE FROM ORGANTABLE WHERE NAME='" + organ_name + "'", DBUtil.oleCon);
                return (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            
        }

        //소속 기관 코드 -> 소속 기관명
       internal static string getOrganName(string organ_code) {

           OleDbCommand cmd = null;

           try
           {
               cmd = new OleDbCommand("SELECT NAME FROM ORGANTABLE WHERE CODE='" + organ_code + "'", DBUtil.oleCon);
               return (string)cmd.ExecuteScalar();
           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
           finally
           {
               cmd.Dispose();
           }
       }


        //사용자 정보 불러오기
       internal static ArrayList getValuerInfo()
       {
           ArrayList data = new ArrayList();
           OleDbCommand cmd = null;
           OleDbDataReader read = null;
           bool bHasRows = false;

           try
           {
               cmd = new OleDbCommand("SELECT * FROM USERS", DBUtil.oleCon);
               read = cmd.ExecuteReader();

               if (read.HasRows)
               {
                   read.Read();
                   for (int i = 0; i < read.FieldCount; i++)
                   {
                       data.Add(read.GetValue(i));
                   }
                   bHasRows = true;
               }
           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
           finally
           {

               if (read.IsClosed == false)
               {
                   read.Close();
               }

           }


           return bHasRows?data:null;
       }

        //다면평가자 정보 DB에 레코드 삽입
        internal static bool insertValuerInfo(ModelValuerInfo mvi){

            OleDbCommand cmd = null;
            string sql = "";
            int affectedRows = 0;

            try
            {
                sql = "INSERT INTO USERS (EVALUER1,EVALUER2,CONFIRMER,ORGAN_CODE,USER_PW,SDATE,EDATE,REGTIME) VALUES ('" + mvi.Evaluer1 + "','" + mvi.Evaluer2 + "','" + mvi.Confirmer + "','" + mvi.Organ + "','" + mvi.Passwd + "','" + mvi.SDate + "','" + mvi.EDate + "','" + mvi.Regtime + "')";                
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                affectedRows = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return affectedRows==1?true:false;
        }

        //다면평가자 정보 DB의 레코드 수정
        internal static bool updateValuerInfo(ModelValuerInfo mvi)
        {

            OleDbCommand cmd = null;
            string sql = "";
            int affectedRows = 0;

            try
            {
                sql = "UPDATE USERS SET "                
                + "EVALUER1='"+mvi.Evaluer1 + "',"
                + "EVALUER2='" + mvi.Evaluer2 + "',"
                + "CONFIRMER='" + mvi.Confirmer + "',"
                + "ORGAN_CODE='"+mvi.Organ + "',"
                + "USER_PW='"+mvi.Passwd + "',"
                + "SDATE='" + mvi.SDate + "',"
                + "EDATE='" + mvi.EDate + "',"
                + "REGTIME='"+mvi.Regtime + "' WHERE INX="+mvi.Inx;
                //MessageBox.Show(sql);
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                affectedRows = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return affectedRows == 1 ? true : false;
        }

        //현재 사용자 성명
        internal static string getCurrentUserName()
        {
            OleDbCommand cmd = null;
            string user_name = "";
            try
            {
                cmd = new OleDbCommand("SELECT EVALUER1 FROM USERS", DBUtil.oleCon);
                user_name = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }


            return user_name;
        }

        //사용자 정보 등록 여부
        internal static bool isRegistedUserInfo()
        {

            OleDbCommand cmd = null;
            int cnt = 0;
            
            bool isRegisted = false;

            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM USERS", DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
                if(cnt > 0)
                {
                    isRegisted = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }


            return isRegisted;

        }

        //비밀번호 업데이트
        internal static bool updatePassWord(int inx, string p)
        {
            OleDbCommand cmd = null;

            try
            {
                cmd = new OleDbCommand("UPDATE USERS SET USER_PW='"+p+"' WHERE INX=" + inx, DBUtil.oleCon);
                if (cmd.ExecuteNonQuery() == 1)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            return false;

        }

        //다면평가대상자 존재 여부
        internal static bool isExistedMTarget(string pcode)
        {

            OleDbCommand cmd = null;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM M_TARGETS WHERE TARGET_PCODE='" + pcode + "'", DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }

            return cnt == 1 ? true : false;

        }


        //다면평가대상자 입력
        internal static bool insertMTargetInfo(int targetType, string pcode, string name, string sex, string subject)
        {

            if (DBUtil.isExistedMTarget(pcode))
            {
                throw new Exception("이미 등록한 대상자입니다.  ");                
            }

            OleDbCommand cmd = null;
            int affectedRow = 0;
            DateTime now = DateTime.Now;
            string sql = "INSERT INTO M_TARGETS (TARGET_TYPE,ORGAN_CODE,TARGET_PCODE,TARGET_NAME,TARGET_SEX,TARGET_SUBJECT,TARGET_REGTIME) VALUES (" + 
                            targetType + "," +
                            "'" + Config.Mvi.Organ + "'," +
                            "'" + pcode + "'," + 
                            "'" + name + "'," +
                            "'" + sex + "'," +
                            "'" + subject + "'," +
                            "'" + now + "')";

            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                affectedRow = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return affectedRow == 1 ? true : false;
        }

        //다면평가대상자 목록을 가져옴
        internal static ArrayList getMTargetList(byte targetType)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT TARGET_PCODE,TARGET_NAME, TARGET_SEX, TARGET_SUBJECT FROM M_TARGETS WHERE TARGET_TYPE=" + targetType + " ORDER BY INX ASC", oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    Hashtable hash = new Hashtable();
                    hash["TARGET_PCODE"] = read.GetString(0);
                    hash["TARGET_NAME"] = read.GetString(1);
                    hash["TARGET_SEX"] = read.GetString(2);
                    hash["TARGET_SUBJECT"] = read.GetString(3);

                    list.Add(hash);
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static ArrayList getMTargetList()
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT TARGET_PCODE,TARGET_NAME, TARGET_SEX, TARGET_SUBJECT FROM M_TARGETS ORDER BY INX ASC", oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    Hashtable hash = new Hashtable();
                    hash["TARGET_PCODE"] = read.GetString(0);
                    hash["TARGET_NAME"] = read.GetString(1);
                    hash["TARGET_SEX"] = read.GetString(2);
                    hash["TARGET_SUBJECT"] = read.GetString(3);

                    list.Add(hash);
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static void deleteMTargetInfo(string p)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM M_TARGETS WHERE TARGET_PCODE='" + p + "'", DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void updateMTargetInfo(int targetType, string pcode, string name, string sex, string subject)
        {
            OleDbCommand cmd = null;
            string sql = "UPDATE M_TARGETS SET TARGET_TYPE=" + targetType + ", TARGET_NAME='" + name + "', TARGET_SEX='" + sex + "', TARGET_SUBJECT='" + subject + "' WHERE TARGET_PCODE='" + pcode + "'";
            try
            {

                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static int getMTargetCount(byte targetType)
        {
            
            OleDbCommand cmd = null;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM M_TARGETS WHERE TARGET_TYPE=" + targetType, DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return cnt;
        }

        internal static int getMTargetCount()
        {

            OleDbCommand cmd = null;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM M_TARGETS", DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return cnt;
        }

        internal static int getMutiCount()
        {

            OleDbCommand cmd = null;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM TMULTITABLE", DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return cnt;
        }

        internal static int getEvalueCount()
        {

            OleDbCommand cmd = null;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKMULTISUM", DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return cnt;
        }

        internal static int getMutiCount(byte targetType)
        {

            OleDbCommand cmd = null;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM TMULTITABLE A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType, DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return cnt;
        }

        internal static int getEvalueCount(byte targetType)
        {

            OleDbCommand cmd = null;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKMULTISUM A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType, DBUtil.oleCon);
                cnt = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return cnt;
        }

        internal static ArrayList getMTargetInfoList(int targetType)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT TARGET_TYPE,TARGET_PCODE,TARGET_NAME,TARGET_SEX,TARGET_SUBJECT FROM M_TARGETS WHERE TARGET_TYPE=" + targetType, DBUtil.oleCon);
                read = cmd.ExecuteReader();
                while (read.Read())
                {
                    ArrayList tmp = new ArrayList();
                    tmp.Add(read.GetValue(0));
                    tmp.Add(read.GetValue(1));
                    tmp.Add(read.GetValue(2));
                    tmp.Add(read.GetValue(3));
                    tmp.Add(read.GetValue(4));
                    data.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return data;
        }

        internal static ArrayList getMTargetPCode(string name)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT TARGET_PCODE, TARGET_NAME, TARGET_SUBJECT FROM M_TARGETS WHERE TARGET_NAME = '" + name + "'", oleCon);
                read = cmd.ExecuteReader();
                while (read.Read())
                {
                    ArrayList tmp = new ArrayList();
                    tmp.Add(read.GetValue(0));
                    tmp.Add(read.GetValue(1));
                    tmp.Add(read.GetValue(2));
                    data.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return data;
        }

        internal static ArrayList getMTargetInfo(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT TARGET_NAME,TARGET_SEX,TARGET_SUBJECT FROM M_TARGETS WHERE TARGET_PCODE='"+pcode+"'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                    data.Add(read.GetValue(2));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return data;
        }



        internal static bool isExistedMultiInfo(string p, string valuer_code)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM MULTITABLE WHERE M_PCODE='" + p + "' AND M_VALUER='" + valuer_code + "'", DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt != 0)
                {
                    b = true;
                }
                //MessageBox.Show("SELECT COUNT(*) FROM MULTITABLE WHERE M_PCODE='" + p + "' AND M_VALUER='" + valuer_code + "'");
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return b;
        }

        /*
         * @breif 다면평가자별 다면평가정보 입력
         * */
        internal static void insertMultiInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            DateTime now = DateTime.Now;
            string sql = "INSERT INTO MULTITABLE (M_PRANK,M_PCODE,M_VALUER,M_ITEM1,M_ITEM2,M_ITEM3,M_ITEM4,M_ITEM5,TOTAL_POINT,REGTIME) VALUES (" +
                data[0] + ",'" +
                data[1] + "','" +
                data[2] + "'," +
                data[3] + "," +
                data[4] + "," +
                data[5] + "," +
                data[6] + "," +
                data[7] + "," +
                data[8] + ",'" +
                now + "')";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /*
         * @breif 다면평가자별 다면평가정보 수정
         * */
        internal static void updateMultiInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            DateTime now = DateTime.Now;
            string sql = "UPDATE MULTITABLE SET M_PRANK=" + data[0] + ",M_ITEM1=" + data[3] + ",M_ITEM2=" + data[4] + ",M_ITEM3=" + data[5] + ",M_ITEM4=" + data[6] + ",M_ITEM5=" + data[7] + ",TOTAL_POINT=" + data[8] + ",REGTIME='" + now + "' WHERE M_PCODE='" + data[1] + "' AND M_VALUER='" + data[2] + "'";
              
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static string getManagerType() {
            OleDbCommand cmd = null;
            string c_type = "";
            try
            {
                cmd = new OleDbCommand("SELECT C_TYPE FROM CONFIG", DBUtil.oleCon);
                c_type = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return c_type;
        }

        internal static DateTime getSDate(string user_type)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            DateTime sdate = DateTime.Now;
            try
            {
                cmd = new OleDbCommand("SELECT SDATE FROM USERS WHERE USER_TYPE='"+user_type+"'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    sdate = (DateTime)read.GetValue(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return sdate;
        }

        internal static DateTime getEDate(string user_type)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            DateTime edate = DateTime.Now;
            try
            {
                cmd = new OleDbCommand("SELECT EDATE FROM USERS WHERE USER_TYPE='" + user_type + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    edate = (DateTime)read.GetValue(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return edate;
        }

        internal static int updateConfig(string userType, DateTime sDate, DateTime eDate)
        {
            OleDbCommand cmd = null;
            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("UPDATE CONFIG SET C_TYPE='"+userType+"',C_SDATE='"+sDate+"',C_EDATE='"+eDate+"' WHERE INX=1", DBUtil.oleCon);
                cnt = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return cnt;
        }

        //다면평가 테이블에서 다면 평가자 개인번호를 중복없이 ArrayList로 입력
        internal static ArrayList getValuerList()
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT DISTINCT(M_VALUER) FROM MULTITABLE", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                while (read.Read())
                {
                    list.Add(read.GetValue(0));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static string getValuerName(string pcode)
        {
            OleDbCommand cmd = null;
            string name = "";
            try
            {
                cmd = new OleDbCommand("SELECT TARGET_NAME FROM M_TARGETS WHERE TARGET_PCODE='"+pcode+"'", DBUtil.oleCon);
                name = (string)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return name;
        }

        internal static double getMElementValue(string fieldName, string pcode, string valuer)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double value = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT " + fieldName + " FROM MULTITABLE WHERE M_PCODE='" + pcode + "' AND M_VALUER='" + valuer + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    value = read.GetDouble(0);
                }
                else
                {
                    value = 0.0;
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return value;
        }

        internal static bool setInitMTargetInfo()
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("DELETE FROM M_TARGETS", DBUtil.oleCon);
                int aRow = cmd.ExecuteNonQuery();
                if (aRow > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return b;
        }

        internal static bool setInitMultiInfo()
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("DELETE FROM MULTITABLE", DBUtil.oleCon);
                int aRow = cmd.ExecuteNonQuery();
                if (aRow > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return b;
        }

        internal static void writeElementsInfo(ArrayList data, string element_type)
        {
            OleDbCommand cmd = null;
            String sql = "";
            DateTime now = DateTime.Now;
            if (isExistElementInfo(data[0].ToString(), element_type) == true)
            {
                sql = "UPDATE SMULTITABLE SET TOTAL_POINT="+data[1]+", STD_POINT="+data[2]+",REGTIME='"+now+"' WHERE PCODE='"+data[0]+"' AND ELEMENT_TYPE='"+element_type+"'";
            }
            else
            {
                sql = "INSERT INTO SMULTITABLE (PCODE,ELEMENT_TYPE,TOTAL_POINT,STD_POINT,REGTIME) VALUES ('"+data[0]+"','"+element_type+"',"+data[1]+","+data[2]+",'"+now+"')";
            }
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static bool isExistElementInfo(string pcode,string element_type)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM SMULTITABLE WHERE PCODE='"+pcode+"' AND ELEMENT_TYPE='"+element_type+"'",DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return b;
        }

        internal static ArrayList getElementInfo(string pcode, string element_type)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {

                cmd = new OleDbCommand("SELECT TOTAL_POINT,STD_POINT FROM SMULTITABLE WHERE PCODE='" + pcode + "' AND ELEMENT_TYPE='"+element_type+"'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return data;

        }

        internal static ArrayList getItemsFromMMultitable(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList list = new ArrayList();
            try
            {
                string sql = "SELECT STD_POINT FROM SMULTITABLE WHERE PCODE='" + pcode + "' ORDER BY ELEMENT_TYPE ASC";
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                read = cmd.ExecuteReader();
                while (read.Read())
                {
                    list.Add(read.GetDouble(0));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static void writeSumInfo(ArrayList list)
        {
            foreach (ArrayList data in list)
            {
                if (DBUtil.isExistedSumInfo(data[0].ToString()) == true)
                {
                    DBUtil.updateSumInfo(data);
                }
                else 
                {
                    DBUtil.insertSumInfo(data);
                }
            }
        }

        private static void insertSumInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            DateTime now = DateTime.Now;
            
            try
            {
                string sql = "INSERT INTO TMULTITABLE (PCODE,TOTAL_POINT,STD_POINT,REGTIME) VALUES ('" + data[0] + "'," + data[1] + "," + data[2] + ",'" + now + "')";
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static void updateSumInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            DateTime now = DateTime.Now;
            string sql = "UPDATE TMULTITABLE SET TOTAL_POINT=" + data[1] + ", STD_POINT=" + data[2] + ", REGTIME='" + now + "' WHERE PCODE='" + data[0] + "'";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static bool isExistedSumInfo(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM TMULTITABLE WHERE PCODE='"+pcode+"'", DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0) {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return b;
        }

        internal static double getItemFromTMultitable(string pcode, string type)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double d = 0.0;
            string field_name = type == "1" ? "TOTAL_POINT" : "STD_POINT";
            try
            {
                string sql = "SELECT " + field_name + " FROM TMULTITABLE WHERE PCODE='" + pcode + "'";
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    d = Double.Parse(read.GetValue(0).ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return d;
        }

        internal static bool getCheckItemFromTMultitable(string pcode, string type)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            bool b = false;
            string field_name = type == "1" ? "CHECK_VALUER1" : "CHECK_VALUER2";
            try
            {
                string sql = "SELECT " + field_name + " FROM TMULTITABLE WHERE PCODE='" + pcode + "'";
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    b = (bool)read.GetValue(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return b;
        }

        internal static ArrayList getRatioNum(int targetType)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT S_SU,S_WO,S_MI,S_YA FROM WRATIO_TABLE WHERE S_NUM=" + getMTargetCount((byte)targetType), DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                    data.Add(read.GetValue(2));
                    data.Add(read.GetValue(3));

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return data;
        }

        internal static ArrayList getTotalSu(Int16 num)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT D_RECORD FROM DISTRIBUTION_TABLE WHERE D_GRADE='수' AND D_NUMBER=" + num + " ORDER BY D_SERIAL ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    data.Add((float)read.GetValue(0));
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return data;
        }

        internal static ArrayList getTotalWo(Int16 num)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT D_RECORD FROM DISTRIBUTION_TABLE WHERE D_GRADE='우' AND D_NUMBER=" + num + " ORDER BY D_SERIAL ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    data.Add((float)read.GetValue(0));
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return data;
        }

        internal static ArrayList getTotalMi(Int16 num)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT D_RECORD FROM DISTRIBUTION_TABLE WHERE D_GRADE='미' AND D_NUMBER=" + num + " ORDER BY D_SERIAL ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    data.Add((float)read.GetValue(0));
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return data;
        }


        internal static void writeWorkValue(ArrayList list)
        {
            if (DBUtil.isExistedWorkValue(list[0].ToString(),list[1].ToString()) == true)
            {
                DBUtil.updateWorkValue(list);
            }
            else
            {
                DBUtil.insertWorkValue(list);
            }
        }

        private static void insertWorkValue(ArrayList list)
        {
            OleDbCommand cmd = null;
            DateTime now = DateTime.Now;

            try
            {
                string sql = "INSERT INTO WORKEVALUETABLE (MANAGER_TYPE,PCODE,ITEM1,ITEM2,ITEM3,ITEM4,ITEM5,EVALUE_POINT,STD_POINT,REGTIME) VALUES ('" +
                list[0] + "','" + list[1] + "'," + list[2] + "," + list[3] + "," + list[4] + "," + list[5] + "," + list[6] +","+ list[7] + "," + list[8] + ",'" + list[9] + "')";
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static void updateWorkValue(ArrayList list)
        {
            OleDbCommand cmd = null;
            DateTime now = DateTime.Now;
            string sql = "UPDATE WORKEVALUETABLE SET ITEM1=" + list[2] + ",ITEM2=" + list[3] + ",ITEM3=" + list[4] + ",ITEM4=" + list[5] + ",ITEM5=" + list[6] + ",EVALUE_POINT=" + list[7] + ",STD_POINT=" + list[8] + ",REGTIME='" + list[9] + "' WHERE MANAGER_TYPE='" + list[0] + "' AND PCODE='" + list[1] + "'";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static bool isExistedWorkValue(string manager_type, string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKEVALUETABLE WHERE MANAGER_TYPE='"+manager_type+"' AND PCODE='" + pcode + "'", DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return b;
        }

        internal static ArrayList getWorkEvalueInfo(string manager_type, string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT ITEM1,ITEM2,ITEM3,ITEM4,ITEM5,EVALUE_POINT,STD_POINT FROM WORKEVALUETABLE WHERE MANAGER_TYPE='"+manager_type+"' AND PCODE='"+pcode+"'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                    data.Add(read.GetValue(2));
                    data.Add(read.GetValue(3));
                    data.Add(read.GetValue(4));
                    data.Add(read.GetValue(5));
                    data.Add(read.GetValue(6));
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return data;
        }

        
        internal static double getWorkControlPoint(string lang, int chk, int chknum)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double d = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT D_RECORD FROM DISTRIBUTION_TABLE WHERE D_GRADE='" + lang + "' AND D_NUMBER=" + chknum + " AND D_SERIAL=" + chk + " ORDER BY D_SERIAL ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    d = Double.Parse(read.GetValue(0).ToString());
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return d;
        }

        internal static void writeWorkControl(ArrayList list)
        {
            if (DBUtil.isExistedWorkControl(list[0].ToString()) == true)
            {
                DBUtil.updateWorkControl(list);
            }
            else
            {
                DBUtil.insertWorkControl (list);
            }
        }

        private static void insertWorkControl(ArrayList list)
        {
            OleDbCommand cmd = null;            
            string sql = "INSERT INTO WORKCONTROLTABLE (PCODE, TOTAL_POINT, CONTROL_POINT,PRANKING, REGTIME ) VALUES ('"+ list[0] +"',"+list[1]+","+list[2]+","+list[3]+",'"+list[4]+"')";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static void updateWorkControl(ArrayList list)
        {
            OleDbCommand cmd = null;
            
            string sql = "UPDATE WORKCONTROLTABLE SET TOTAL_POINT='" + list[1] + "', CONTROL_POINT=" +  list[2] + ", PRANKING=" + list[3]+ ", REGTIME='" + list[4] + "'  WHERE PCODE='" + list[0] + "'";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static bool isExistedWorkControl(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKCONTROLTABLE WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return b;
        }

        internal static ArrayList getWorkControlInfo(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT TOTAL_POINT,CONTROL_POINT,PRANKING FROM WORKCONTROLTABLE WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                    data.Add(read.GetValue(2));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return data;
        }

        internal static ArrayList getWorkControlTargetsInfo(byte targetType)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT A.PCODE FROM WORKCONTROLTABLE A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType +" ORDER BY A.PRANKING ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();
               
                while (read.Read())
                {
                    ArrayList tmp = new ArrayList();                    
                    tmp.Add(read.GetValue(0));
                    data.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return data;
        }

        internal static string getMTargetName(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            string target_name = "";
            try
            {
                cmd = new OleDbCommand("SELECT TARGET_NAME FROM M_TARGETS WHERE TARGET_PCODE='" + pcode + "'", DBUtil.oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    target_name = reader.GetString(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                //자원해제
                reader.Dispose();
                cmd.Dispose();
                //객체소멸
                reader = null;
                cmd = null;
            }

            return target_name;
        }

        internal static double getWorkEvaluePoint(string type, string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double evalue_point = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT EVALUE_POINT FROM WORKEVALUETABLE WHERE MANAGER_TYPE='" + type + "' AND PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    evalue_point = read.GetDouble(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return evalue_point;
        }

        internal static double getMultiPoint(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double total_point = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT TOTAL_POINT FROM TMULTITABLE WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    total_point =  read.GetDouble(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return total_point;
        }

        internal static double getMultiControl(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double std_point = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT STD_POINT FROM TMULTITABLE WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    std_point = read.GetDouble(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return std_point;
        }

        internal static ArrayList getPersonalEvalue2009(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM PERSONAL_EVALUATION WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    int cnt = read.GetInt16(3);

                    for (int i = 0; i < cnt; i++)
                    {
                        list.Add(read.GetDouble(i + 4));
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static bool writeWorkMultiInfo(ArrayList list)
        {
            if (DBUtil.isExistedWorkMulti(list[0].ToString()) == true)
            {
                if (DBUtil.updateWorkMuilti(list) == true)
                {
                    return true;
                }
            }
            else
            {
                if (DBUtil.insertWorkMulti(list) == true)
                {
                    return true;
                }
            }

            return false;
        }

        private static bool insertWorkMulti(ArrayList list)
        {
            OleDbCommand cmd = null;

            string sql = "INSERT INTO WORKMULTISUM (PCODE,VALUER1_POINT,VALUER2_POINT,VALUER3_POINT,TOTAL_POINT,CONTROL_POINT,PRANKING,REGTIME) VALUES ('" + list[0] + "'," + list[1] + "," + list[2] + "," + list[3] + "," + list[4] + "," + list[5] + "," + list[6] + ",'" + list[7] + "')";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                if (cmd.ExecuteNonQuery() > 0)
                {

                    return true;

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return false;
        }

        private static bool updateWorkMuilti(ArrayList list)
        {
            OleDbCommand cmd = null;

            string sql = "UPDATE WORKMULTISUM SET VALUER1_POINT=" + list[1] + ",VALUER2_POINT=" + list[2] + ",VALUER3_POINT=" + list[3] + ",TOTAL_POINT=" + list[4] + ", CONTROL_POINT=" + list[5] + ",PRANKING=" + list[6] + ",REGTIME='" + list[7] + "'  WHERE PCODE='" + list[0] + "'";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return false;
        }

        private static bool isExistedWorkMulti(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKMULTISUM WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }

            return b;
        }

        internal static bool isExistedWorkMultiInfo(byte targetType)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKMULTISUM A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType, DBUtil.oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }

            return b;
        }

        internal static ArrayList getWorkMultiSumInfo(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList info = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM WORKMULTISUM WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    info.Add(read.GetValue(0));
                    info.Add(read.GetValue(1));
                    info.Add(read.GetValue(2));
                    info.Add(read.GetValue(3));
                    info.Add(read.GetValue(4));
                    info.Add(read.GetValue(5));
                    info.Add(read.GetValue(6));
                    info.Add(read.GetValue(7));
                    info.Add(read.GetValue(8));
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return info;
        }

        internal static double getWorkMultiSumValue1(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double d = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT CONTROL_POINT FROM WORKMULTISUM WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    d = Double.Parse(read.GetValue(0).ToString());
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally {
                read.Close();
                cmd.Dispose();
            }
            return d;
        }

        internal static Int32 getWorkMultiSumValue2(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            int i = 0;
            try
            {
                cmd = new OleDbCommand("SELECT PRANKING FROM WORKMULTISUM WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    i = Int32.Parse(read.GetValue(0).ToString());
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return i;
        }

        internal static bool setInitMultiInfo(int type)
        {
            OleDbCommand cmd = null;
            bool b = false;
            string table_name = "";
            switch (type)
            {
                case 1:
                    table_name = "MULTITABLE";
                    break;
                case 2:
                    table_name = "SMULTITABLE";
                    break;
                case 3:
                    table_name = "TMULTITABLE";
                    break;
                case 4:
                    table_name = "WORKEVALUETABLE";
                    break;
                case 5:
                    table_name = "WORKCONTROLTABLE";
                    break;
                case 6:
                    table_name = "WORKMULTISUM";
                    break;
            }
            try
            {
                cmd = new OleDbCommand("DELETE FROM " + table_name, DBUtil.oleCon);
                int aRow = cmd.ExecuteNonQuery();
                if (aRow > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return b;
        }

        //다면평가표 테이블에 데이터 존재 여부
        internal static bool existDataTMultiTable() {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM TMULTITABLE", DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return b;
        }

        internal static int writeDemeritInfo(ArrayList arr)
        {
            if (existedDemeritData(arr[0].ToString()) == true)
            {
                return updateDemeritInfo(arr);
            }
            else
            {
                return insertDemeritInfo(arr);
            }
        }

        private static int insertDemeritInfo(ArrayList list)
        {
            OleDbCommand cmd = null;

            string sql = "INSERT INTO DEMERITTABLE (D_PCODE,D_COMMENT,D_ORIGINTOTAL,D_DEMERITPOINT,D_CHANGETOTAL,REGTIME) VALUES ('" + list[0] + "','" + list[1] + "'," + list[2] + "," + list[3] + "," + list[4] + ",'" + list[5] + "')";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        private static int updateDemeritInfo(ArrayList arr)
        {
            OleDbCommand cmd = null;

            string sql = "UPDATE DEMERITTABLE SET D_COMMENT='"+arr[1]+"',D_ORIGINTOTAL="+arr[2]+",D_DEMERITPOINT="+arr[3]+",D_CHANGETOTAL="+arr[4]+",REGTIME='"+arr[5]+"' WHERE D_PCODE='"+arr[0]+"'";
            try
            {
                cmd = new OleDbCommand(sql, DBUtil.oleCon);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static bool existedDemeritData(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM DEMERITTABLE WHERE D_PCODE='"+pcode+"'", oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return b;
        }

        internal static int deleteDemeritInfo(ArrayList arr)
        {
            if (existedDemeritData( arr[0].ToString()) == true)
            {
                OleDbCommand cmd = null;

                string sql = "DELETE FROM DEMERITTABLE WHERE D_PCODE='"+arr[0]+"'";
                try
                {
                    cmd = new OleDbCommand(sql, DBUtil.oleCon);
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else
            {
                throw new Exception("저장된 정보가 없습니다.    ");
            }
        }

        internal static ArrayList getDemeritInfo(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList arr = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT D_COMMENT,D_ORIGINTOTAL, D_DEMERITPOINT, D_CHANGETOTAL, REGTIME FROM DEMERITTABLE WHERE D_PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    arr.Add(read.GetValue(0));
                    arr.Add(read.GetValue(1));
                    arr.Add(read.GetValue(2));
                    arr.Add(read.GetValue(3));
                    arr.Add(read.GetValue(4));

                    return arr;
                }           

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return arr;
        }

        //MULTITABLE 평정자 수
        internal static int getWorkEvaluerCount()
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            int cnt = 0;
            try
            {
                cmd = new OleDbCommand("SELECT DISTINCT(MANAGER_TYPE) FROM WORKEVALUETABLE", DBUtil.oleCon);
                read = cmd.ExecuteReader();
                while (read.Read())
                {
                    cnt++;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return cnt;
        }

        internal static ArrayList getMTargetListFromSMULTITABLE(byte targetType, string element_type)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT A.PCODE FROM SMULTITABLE A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " AND A.ELEMENT_TYPE='" + element_type + "' ORDER BY A.TOTAL_POINT DESC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {

                    ArrayList tmp = new ArrayList();
                    tmp.Add(read.GetValue(0));

                    list.Add(tmp);
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static ArrayList getItemFromSMULTITABLE(string element_type, string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT TOTAL_POINT, STD_POINT FROM SMULTITABLE WHERE ELEMENT_TYPE='" + element_type + "' AND PCODE='"+pcode+"'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    list.Add(read.GetValue(0));
                    list.Add(read.GetValue(1));
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }


        internal static ArrayList getMTargetListFromTMULTITABLE(byte targetType)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT A.PCODE FROM TMULTITABLE A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " ORDER BY A.STD_POINT DESC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    list.Add(read.GetString(0));
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }


        internal static ArrayList getMTargetFromWorkEvalueSum(byte targetType)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT A.PCODE FROM WORKMULTISUM A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " ORDER BY A.PRANKING ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    list.Add(read.GetValue(0));
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static ArrayList getMTargetFromWorkControlTable()
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT PCODE FROM WORKCONTROLTABLE ORDER BY PRANKING ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    list.Add(read.GetValue(0));
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static ArrayList getMTargetArrayFromWorkControlTable(byte targetType)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT A.INX, A.PCODE, B.TARGET_NAME, B.TARGET_SEX, B.TARGET_SUBJECT FROM WORKCONTROLTABLE A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " ORDER BY A.PRANKING ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                    data.Add(read.GetValue(2));
                    data.Add(read.GetValue(3));
                    data.Add(read.GetValue(4));

                    list.Add(data);
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static ArrayList getMTargetFromWorkEvalueTable(byte targetType, string type)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT A.INX, A.PCODE, B.TARGET_NAME, B.TARGET_SEX, B.TARGET_SUBJECT FROM WORKEVALUETABLE A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " AND A.MANAGER_TYPE='" + type + "' ORDER BY A.EVALUE_POINT DESC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(read.GetValue(0));
                    data.Add(read.GetValue(1));
                    data.Add(read.GetValue(2));
                    data.Add(read.GetValue(3));
                    data.Add(read.GetValue(4));

                    list.Add(data);
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static ArrayList getMTargetFromWorkMultiSumTable()
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            ArrayList list = new ArrayList();

            try
            {
                cmd = new OleDbCommand("SELECT PCODE FROM WORKMULTISUM ORDER BY PRANKING ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    list.Add(read.GetValue(0));
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return list;
        }

        internal static string getGradeName(double d)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;

            try
            {
                cmd = new OleDbCommand("SELECT DISTINCT(D_GRADE) FROM DISTRIBUTION_TABLE WHERE D_RECORD="+ d, DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    return read.GetValue(0).ToString();
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);

            }

            return "" ;
        }

        internal static ArrayList getWorkMultiTargetsInfo(byte targetType)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            ArrayList data = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT A.PCODE FROM WORKMULTISUM A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " ORDER BY A.PRANKING ASC", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                while (read.Read())
                {
                    ArrayList tmp = new ArrayList();
                    tmp.Add(read.GetValue(0));
                    data.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }

            return data;
        }

        internal static double getWorkMultiSumValue0(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double d = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT TOTAL_POINT FROM WORKMULTISUM WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    d = Double.Parse(read.GetValue(0).ToString());
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return d;
        }

        internal static string stateEvalueTable()
        {
            OleDbCommand cmd = null;
            string state = "";

            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKEVALUETABLE WHERE MANAGER_TYPE='0'", DBUtil.oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    state = "평정완료";
                }
                else
                {
                    state = "평정안함";
                }

                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKEVALUETABLE WHERE MANAGER_TYPE='0'", DBUtil.oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    state += "/확인완료";
                }
                else
                {
                    state += "/확인안함";
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return state;
        }

        internal static string stateWorkSumTable()
        {
            OleDbCommand cmd = null;
            string state = "";

            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKMULTISUM", DBUtil.oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    state = "완료";
                }
                else
                {
                    state = "미완료";
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return state;
        }

        internal static string getVersion(int type)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            string version = "";
            try
            {
                cmd = new OleDbCommand("SELECT VERSION FROM VERSIONINFO WHERE UTYPE=" + type, DBUtil.oleCon); 
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    version = read.GetValue(0).ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return version;
            
        }

        internal static bool isExistTMultiInfo()
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM TMULTITABLE", DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return b;
        }

        internal static bool isExistWorkEvalueInfo(byte targetType, string type)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKEVALUETABLE A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " AND A.MANAGER_TYPE='" + type + "'", DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return b;
        }

        internal static bool isExistWorkControlInfo()
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKCONTROLTABLE", DBUtil.oleCon);
                int cnt = (int)cmd.ExecuteScalar();
                if (cnt > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return b;
        }


        internal static int getStatisicsTotal(byte targetType, string mType, double start, double end)
        {
            OleDbCommand cmd = null;
            int i = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKEVALUETABLE A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " AND A.MANAGER_TYPE='" + mType + "' AND (ROUND(A.EVALUE_POINT,2) >=" + start + " AND ROUND(A.EVALUE_POINT,2) < " + end + ")", DBUtil.oleCon);
                i = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return i;
        }

        internal static int getStatisicsTotal(byte targetType, double start, double end)
        {
            OleDbCommand cmd = null;
            int i = 0;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM WORKMULTISUM A, M_TARGETS B WHERE A.PCODE=B.TARGET_PCODE AND B.TARGET_TYPE=" + targetType + " AND (ROUND(A.CONTROL_POINT,2) >=" + start + " AND ROUND(A.CONTROL_POINT,2) < " + end + ")", DBUtil.oleCon);
                i = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return i;
        }

        internal static double getTotalPointFromWorkControlTable(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double point = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT TOTAL_POINT FROM WORKCONTROLTABLE WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    point = (double)read.GetValue(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return point;
        }

        internal static double getControlPointFromWorkControlTable(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double point = 0.0;
            try
            {
                cmd = new OleDbCommand("SELECT CONTROL_POINT FROM WORKMULTISUM WHERE PCODE='" + pcode + "'", DBUtil.oleCon);
                read = cmd.ExecuteReader();

                if (read.Read())
                {
                    point = (double)read.GetValue(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                read.Close();
                cmd.Dispose();
            }
            return point;
        }

        internal static ArrayList getListFromCareerTable(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM CAREERTABLE WHERE PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(reader.GetInt32(0));
                    data.Add(reader.GetString(1));
                    data.Add(reader.GetString(2));
                    data.Add(reader.GetString(3));
                    data.Add(reader.GetString(4));
                    data.Add(reader.GetDateTime(5));
                    data.Add(reader.GetDateTime(6));
                    data.Add(reader.GetDateTime(7));
                    data.Add(reader.GetDateTime(8));
                    data.Add(reader.GetInt32(9));
                    data.Add(reader.GetInt32(10));
                    data.Add(reader.GetDouble(11));
                    data.Add(reader.GetDouble(12));
                    data.Add(reader.GetDouble(13));
                    data.Add(reader.GetDateTime(14));

                    list.Add(data);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "\n\n" + ex.StackTrace);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }


        internal static ArrayList getListFromEduTable(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM EDUTABLE WHERE PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(reader.GetValue(0));
                    data.Add(reader.GetValue(1));
                    data.Add(reader.GetValue(2));
                    data.Add(reader.GetValue(3));
                    data.Add(reader.GetValue(4));
                    data.Add(reader.GetValue(5));
                    data.Add(reader.GetValue(6));
                    data.Add(reader.GetValue(7));
                    data.Add(reader.GetValue(8));
                    data.Add(reader.GetValue(9));
                    data.Add(reader.GetValue(10));
                    data.Add(reader.GetValue(11));

                    list.Add(data);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static ArrayList getListFromResearchTable(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM RESEARCHTABLE WHERE PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(reader.GetValue(0));
                    data.Add(reader.GetValue(1));
                    data.Add(reader.GetValue(2));
                    data.Add(reader.GetValue(3));
                    data.Add(reader.GetValue(4));
                    data.Add(reader.GetValue(5));
                    data.Add(reader.GetValue(6));
                    data.Add(reader.GetValue(7));
                    data.Add(reader.GetValue(8));

                    list.Add(data);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static ArrayList getListFromScholarTable(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM SCHOLARTABLE WHERE PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ArrayList data = new ArrayList();
                    data.Add(reader.GetValue(0));
                    data.Add(reader.GetValue(1));
                    data.Add(reader.GetValue(2));
                    data.Add(reader.GetValue(3));
                    data.Add(reader.GetValue(4));
                    data.Add(reader.GetValue(5));
                    data.Add(reader.GetValue(6));
                    data.Add(reader.GetValue(7));
                    data.Add(reader.GetValue(8));

                    list.Add(data);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static ArrayList getListFromAdditionTable(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM ADDITIONTABLE WHERE PCODE='" + pcode + "' ORDER BY ADD_ORDER ASC", oleCon);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Hashtable hash = new Hashtable();
                    hash["INX"] = reader.GetInt32(0);
                    hash["PCODE"] = reader.GetString(1);
                    hash["ADD_GROUP"] = reader.GetString(2);
                    hash["ADD_ITEM"] = reader.GetString(3);
                    hash["ADD_ITEM_CONTENT"] = reader.GetString(4);
                    hash["ADD_ORGAN"] = reader.GetString(5);
                    hash["ADD_START"] = reader.IsDBNull(6) ? "1900-01-01" : reader.GetString(6);
                    hash["ADD_END"] = reader.IsDBNull(7) ? "1900-01-01" : reader.GetString(7);
                    hash["TARGET_START"] = reader.IsDBNull(8) ? "1900-01-01" : reader.GetString(8);
                    hash["TARGET_END"] = reader.IsDBNull(9) ? "1900-01-01" : reader.GetString(9);
                    hash["ADD_MONTH"] = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
                    hash["ADD_DAY"] = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
                    hash["MONTHLY_POINT"] = reader.IsDBNull(12) ? 0.0 : reader.GetDouble(12);
                    hash["DAILY_POINT"] = reader.IsDBNull(13) ? 0.0 : reader.GetDouble(13);
                    hash["TOTAL_POINT"] = reader.GetDouble(14);
                    hash["ADD_ORDER"] = reader.IsDBNull(16) ? 0 : reader.GetInt32(16);

                    list.Add(hash);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static void deleteFromCareerTable(string pcode)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM CAREERTABLE WHERE PCODE='" + pcode + "'", oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void deleteFromEduTable(string pcode)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM EDUTABLE WHERE PCODE='" + pcode + "'", oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void deleteFromResearchTable(string pcode)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM RESEARCHTABLE WHERE PCODE='" + pcode + "'", oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void deleteFromScholarTable(string pcode)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM SCHOLARTABLE WHERE PCODE='" + pcode + "'", oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void deleteFromAdditionTable(string pcode)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DELETE FROM ADDITIONTABLE WHERE PCODE='" + pcode + "'", oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void insertCareerInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO CAREERTABLE (PCODE, CAREER_TYPE, CAREER_LEVEL, CAREER_ORGAN, WORK_START, WORK_END, EX_START, EX_END, WORK_MONTH, WORK_DAY, MONTH_POINT, DAY_POINT, CAREER_POINT, REGTIME) VALUES (" +
                    "'" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "'," + data[8] + "," + data[9] + "," + data[10] + "," + data[11] + "," + data[12] + ",'" + data[13] + "')";
                cmd = new OleDbCommand(sql, oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void insertEduInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {   
                string sql = "INSERT INTO EDUTABLE (PCODE,EDU_TYPE,EDU_ORGAN,EDU_NAME,EDU_NO,EDU_START,EDU_END,EDU_TIME,EDU_JUMSU,EDU_POINT,REGTIME) VALUES (" +
                    "'" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "'," + data[7] + "," + data[8] + "," + data[9] + ",'" + data[10] + "')";
                cmd = new OleDbCommand(sql, oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void insertResearchInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO RESEARCHTABLE (PCODE,RES_ORGAN, RES_NAME,RES_DATE, RES_SCALE, RES_LEVEL, RES_NUM, RES_POINT, REGTIME) VALUES (" +
                    "'" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "'," + data[7] + ",'" + data[8] + "')";
                cmd = new OleDbCommand(sql, oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void insertScholarInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO SCHOLARTABLE (PCODE,SCH_TYPE, SCH_RELATION,SCH_NAME,SCH_ORGAN,SCH_DATE,SCH_POINT, REGTIME) VALUES (" +
                    "'" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "'," + data[6] + ",'" + data[7] + "')";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void insertAdditionInfo(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO ADDITIONTABLE (PCODE,ADD_GROUP,ADD_ITEM,ADD_ITEM_CONTENT,ADD_ORGAN,ADD_START,ADD_END,TARGET_START,TARGET_END,ADD_MONTH,ADD_DAY,MONTHLY_POINT,DAILY_POINT,TOTAL_POINT, REGTIME, ADD_ORDER) VALUES (" +
                    "'" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "','" + data[8] + "'," + data[9] + "," + data[10] + "," + data[11] + "," + data[12] + "," + data[13] + ",'" + data[14] + "'," + data[15] + ")";
                cmd = new OleDbCommand(sql, oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void insertListType1(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO LIST_TYPE1 (L_PCODE, L_ORGAN, L_PNO, L_SEX, L_POSITION, L_KNAME, L_CNAME ) VALUES (" +
                    "'" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "')";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void updateListType1(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "UPDATE LIST_TYPE1 SET L_ORGAN='" + data[1] + "', L_PNO='" + data[2] + "', L_SEX='" + data[3] + "', L_POSITION='" + data[4] + "', L_KNAME='" + data[5] + "', L_CNAME='" + data[6] + "' WHERE L_PCODE='" + data[0] + "'";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static ArrayList getListFromListType1(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM LIST_TYPE1 WHERE L_PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    list.Add(reader.GetValue(1));
                    list.Add(reader.GetValue(2));
                    list.Add(reader.GetValue(3));
                    list.Add(reader.GetValue(4));
                    list.Add(reader.GetValue(5));
                    list.Add(reader.GetValue(6));
                    list.Add(reader.GetValue(7));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static bool isExistedListType1(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM LIST_TYPE1 WHERE L_PCODE='" + pcode + "'", oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return b;
        }

        internal static void insertListType2(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO LIST_TYPE2 (L_PCODE, L_LICENSE, L_LICENSENO, L_LICENSEDATE, L_POSITIONYEAR, L_POSITIONMONTH, L_TOTALYEAR, L_TOTALMONTH) VALUES (" +
                    "'" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "'," + data[4] + "," + data[5] + "," + data[6] + "," + data[7] + ")";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void updateListType2(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "UPDATE LIST_TYPE2 SET L_LICENSE='" + data[1] + "', L_LICENSENO='" + data[2] + "', L_LICENSEDATE='" + data[3] + "', L_POSITIONYEAR=" + data[4] + ", L_POSITIONMONTH=" + data[5] + ", L_TOTALYEAR=" + data[6] + ", L_TOTALMONTH=" + data[7] + " WHERE L_PCODE='" + data[0] + "'";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static bool isExistedListType2(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM LIST_TYPE2 WHERE L_PCODE='" + pcode + "'", oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return b;
        }

        internal static ArrayList getListFromListType2(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM LIST_TYPE2 WHERE L_PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    list.Add(reader.GetValue(1));
                    list.Add(reader.GetValue(2));
                    list.Add(reader.GetValue(3));
                    list.Add(reader.GetValue(4));
                    list.Add(reader.GetValue(5));
                    list.Add(reader.GetValue(6));
                    list.Add(reader.GetValue(7));
                    list.Add(reader.GetValue(8));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static void insertListType3(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO LIST_TYPE3 (L_PCODE, L_APPOINTSUBJECT, L_UNIV1, L_UNIV2, L_UNIV3, L_GRAD1, L_GRAD2, L_GRAD3, L_TEST1, L_TEST2 ) VALUES (" +
                    "'" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "','" + data[8] + "','" + data[9] + "')";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void updateListType3(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "UPDATE LIST_TYPE3 SET L_APPOINTSUBJECT='" + data[1] + "', L_UNIV1='" + data[2] + "', L_UNIV2='" + data[3] + "', L_UNIV3='" + data[4] + "', L_GRAD1='" + data[5] + "', L_GRAD2='" + data[6] + "', L_GRAD3='" + data[7] + "', L_TEST1='" + data[8] + "', L_TEST2='" + data[9] + "' WHERE L_PCODE='" + data[0] + "'";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static bool isExistedListType3(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM LIST_TYPE3 WHERE L_PCODE='" + pcode + "'", oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return b;
        }

        internal static ArrayList getListFromListType3(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM LIST_TYPE3 WHERE L_PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    list.Add(reader.GetValue(1));
                    list.Add(reader.GetValue(2));
                    list.Add(reader.GetValue(3));
                    list.Add(reader.GetValue(4));
                    list.Add(reader.GetValue(5));
                    list.Add(reader.GetValue(6));
                    list.Add(reader.GetValue(7));
                    list.Add(reader.GetValue(8));
                    list.Add(reader.GetValue(9));
                    list.Add(reader.GetValue(10));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static void insertListType4(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "INSERT INTO LIST_TYPE4 (L_PCODE, L_EVALUEPOINT, L_CAREERPOINT, L_EDUPOINT, L_RESPOINT, L_SCHPOINT, L_ADDPOINT, L_TOTALPOINT, L_ETC) VALUES (" +
                    "'" + data[0] + "'," + data[1] + "," + data[2] + "," + data[3] + "," + data[4] + "," + data[5] + "," + data[6] + "," + data[7] + ",'" + data[8] + "')";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void updateListType4(ArrayList data)
        {
            OleDbCommand cmd = null;
            try
            {
                string sql = "UPDATE LIST_TYPE4 SET L_EVALUEPOINT=" + data[1] + ", L_CAREERPOINT=" + data[2] + ", L_EDUPOINT=" + data[3] + ", L_RESPOINT=" + data[4] + ", L_SCHPOINT=" + data[5] + ", L_ADDPOINT=" + data[6] + ", L_TOTALPOINT=" + data[7] + ", L_ETC='" + data[8] + "' WHERE L_PCODE='" + data[0] + "'";
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static bool isExistedListType4(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM LIST_TYPE4 WHERE L_PCODE='" + pcode + "'", oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return b;
        }

        internal static ArrayList getListFromListType4(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM LIST_TYPE4 WHERE L_PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    list.Add(reader.GetValue(0));
                    list.Add(reader.GetValue(1));
                    list.Add(reader.GetValue(2));
                    list.Add(reader.GetValue(3));
                    list.Add(reader.GetValue(4));
                    list.Add(reader.GetValue(5));
                    list.Add(reader.GetValue(6));
                    list.Add(reader.GetValue(7));
                    list.Add(reader.GetValue(8));
                    list.Add(reader.GetValue(9));
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static string getSubjectInfo(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            string subject = "";
            try
            {
                cmd = new OleDbCommand("SELECT TARGET_SUBJECT FROM M_TARGETS WHERE TARGET_PCODE='" + pcode + "'", oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    subject = reader.GetValue(0).ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return subject;
        }

        internal static void dropTable(string tableName)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand("DROP TABLE " + tableName, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal static void createTable(string sql)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        internal static void alterTable(string sql)
        {
            OleDbCommand cmd = null;
            try
            {
                cmd = new OleDbCommand(sql, oleCon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal  static string getExCareerInfo(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            string exCareerinfo = "";
            try
            {
                cmd = new OleDbCommand("SELECT EX_START, EX_END FROM CAREERTABLE WHERE PCODE='" + pcode + "'", oleCon);
                read = cmd.ExecuteReader();
                DateTime start = DateTime.Parse("2009-12-31");
                DateTime end = DateTime.Parse("1900-01-01");
                while (read.Read())
                {
                    string tmpStart = read.GetValue(0).ToString().Substring(0, 10);
                    string tmpEnd = read.GetValue(1).ToString().Substring(0, 10);

                    DateTime dtStart = DateTime.Parse(tmpStart);
                    DateTime dtEnd = DateTime.Parse(tmpEnd);

                    if (dtEnd.CompareTo(end) == 0) continue;

                    if (start.CompareTo(dtStart) > 0)
                    {
                        if (dtStart.CompareTo(DateTime.Parse("1900-01-01")) != 0)
                        {
                            start = dtStart;
                        }
                    }
                    if (end.CompareTo(dtEnd) < 0) end = dtEnd;
                }

                if (start.Equals(DateTime.Parse("2009-12-31")) || end.Equals(DateTime.Parse("1900-01-01")))
                {
                    exCareerinfo = "";
                }
                else
                {
                    exCareerinfo = start.ToString().Substring(0, 10) + "~\n" + end.ToString().Substring(0, 10);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return exCareerinfo;
        }

        /***********************************************
         * 동명이인확인: getSameNameExist(string name) *
         ***********************************************/
        internal static bool getSameNameExist(string name)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM M_TARGETS WHERE TARGET_NAME='" + name +"'", oleCon);
                if ((int)cmd.ExecuteScalar() > 1)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return b;
        }

        //학위총점
        internal static double getScholarPoint(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader read = null;
            double d = 0.0;
            try
            {
                string sql = "SELECT SUM(SCH_POINT) FROM SCHOLARTABLE WHERE PCODE='" + pcode + "'";
                cmd = new OleDbCommand(sql, oleCon);
                read = cmd.ExecuteReader();
                if (read.Read())
                {
                    //MessageBox.Show(read.GetValue(0).ToString());
                    if (read.GetValue(0).ToString().Equals("") == false)
                    {
                        double tmpValue = Double.Parse(read.GetValue(0).ToString());
                        if (tmpValue > 2.0)
                        {
                            d = 2.0;
                        }
                        else
                        {
                            d = tmpValue;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return d;
        }

        internal static ArrayList getCandidates(int jobLevel)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList candidates = new ArrayList();

            try
            {
                string sql = "";

                if (jobLevel == 0)
                {
                    sql = "SELECT TARGET_PCODE AS PCODE,TARGET_NAME AS NAME,TARGET_SEX AS SEX FROM M_TARGETS ORDER BY TARGET_NAME ASC";
                }
                else
                {
                    sql = "SELECT PCODE, NAME, SEX FROM P_TARGETS ORDER BY NAME ASC";
                }
                cmd = new OleDbCommand(sql, oleCon);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Hashtable tmp = new Hashtable();
                    tmp.Add(reader.GetName(0), reader.GetValue(0));
                    tmp.Add(reader.GetName(1), reader.GetValue(1));
                    tmp.Add(reader.GetName(2), reader.GetValue(2));

                    candidates.Add(tmp);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Dispose();
                cmd.Dispose();

                reader = null;
                cmd = null;
            }

            return candidates;
        }

        internal static Hashtable getEvalueList(int targetType, string refDate)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            Hashtable tmp = new Hashtable();

            try
            {
                if (targetType == 1) refDate = "2010-01-31";
                string sql = "SELECT * FROM EVALUATION_REFERENCE WHERE REF_DATE='" + refDate + "'";

                cmd = new OleDbCommand(sql, oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    
                    tmp.Add(reader.GetName(0), reader.GetValue(0));
                    tmp.Add(reader.GetName(1), reader.GetValue(1));
                    tmp.Add(reader.GetName(2), reader.GetValue(2));
                    tmp.Add(reader.GetName(3), reader.GetValue(3));
                    tmp.Add(reader.GetName(4), reader.GetValue(4));
                    tmp.Add(reader.GetName(5), reader.GetValue(5));
                    tmp.Add(reader.GetName(6), reader.GetValue(6));
                    tmp.Add(reader.GetName(7), reader.GetValue(7));
                    tmp.Add(reader.GetName(8), reader.GetValue(8));
                    tmp.Add(reader.GetName(9), reader.GetValue(9));
                    tmp.Add(reader.GetName(10), reader.GetValue(10));
                    tmp.Add(reader.GetName(11), reader.GetValue(11));

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Dispose();
                cmd.Dispose();

                reader = null;
                cmd = null;
            }

            return tmp;
        }

        internal static Hashtable getPersonalEvalue(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            Hashtable tmp = new Hashtable();

            try
            {
                string sql = "SELECT * FROM PERSONAL_EVALUATION WHERE PCODE='" + pcode + "'";

                cmd = new OleDbCommand(sql, oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {

                    tmp.Add(reader.GetName(0), reader.GetValue(0));
                    tmp.Add(reader.GetName(1), reader.GetValue(1));
                    tmp.Add(reader.GetName(2), reader.GetValue(2));
                    tmp.Add(reader.GetName(3), reader.GetValue(3));
                    tmp.Add(reader.GetName(4), reader.GetValue(4));
                    tmp.Add(reader.GetName(5), reader.GetValue(5));
                    tmp.Add(reader.GetName(6), reader.GetValue(6));
                    tmp.Add(reader.GetName(7), reader.GetValue(7));
                    tmp.Add(reader.GetName(8), reader.GetValue(8));
                    tmp.Add(reader.GetName(9), reader.GetValue(9));
                    tmp.Add(reader.GetName(10), reader.GetValue(10));
                    tmp.Add(reader.GetName(11), reader.GetValue(11));
                    tmp.Add(reader.GetName(12), reader.GetValue(12));
                    tmp.Add(reader.GetName(13), reader.GetValue(13));
                    tmp.Add(reader.GetName(14), reader.GetValue(14));

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Dispose();
                cmd.Dispose();

                reader = null;
                cmd = null;
            }

            return tmp;
        }

        internal static bool saveEvalueInfo(Hashtable hash)
        {
            bool b = false;

            if (isExistedEvalueInfo(hash))
            {
                b = updateEvalueInfo(hash);
            }
            else
            {
                b = insertEvalueInfo(hash);
            }

            return b;
        }

        private static bool insertEvalueInfo(Hashtable hash)
        {
            OleDbCommand cmd = null;
            int affectedRow = 0;
            try
            {
                string sql = "INSERT INTO PERSONAL_EVALUATION (PCODE, REF_DATE, POINT_CNT, POINT1, POINT2, POINT3, POINT4, POINT5, POINT6, POINT7, POINT8, POINT9, POINT10, TOTAL_POINT) VALUES ('" +
                            hash["PCODE"] + "','"+
                            hash["REF_DATE"] + "'," + 
                            hash["POINT_CNT"] + "," + 
                            hash["POINT1"] + "," +
                            hash["POINT2"] + "," +
                            hash["POINT3"] + "," +
                            hash["POINT4"] + "," +
                            hash["POINT5"] + "," +
                            hash["POINT6"] + "," +
                            hash["POINT7"] + "," +
                            hash["POINT8"] + "," +
                            hash["POINT9"] + "," +
                            hash["POINT10"] + "," +
                            hash["TOTAL_POINT"] +
                            ")";

                cmd = new OleDbCommand(sql, oleCon);
                affectedRow = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {                
                throw new Exception(ex.Message);
            }

            return affectedRow > 0;
        }

        private static bool updateEvalueInfo(Hashtable hash)
        {
            OleDbCommand cmd = null;
            int affectedRow = 0;
            try
            {
                string sql = "UPDATE PERSONAL_EVALUATION SET REF_DATE='" + hash["REF_DATE"] + "', POINT_CNT=" + hash["POINT_CNT"] +
                    ", POINT1=" + hash["POINT1"] + ", POINT2=" + hash["POINT2"] + ", POINT3=" + hash["POINT3"] + ", POINT4=" + hash["POINT4"] +
                    ", POINT5=" + hash["POINT5"] + ", POINT6=" + hash["POINT6"] + ", POINT7=" + hash["POINT7"] + ", POINT8=" + hash["POINT8"] + ", POINT9=" + hash["POINT9"] +
                    ", POINT10=" + hash["POINT10"] + ", TOTAL_POINT=" + hash["TOTAL_POINT"] + " WHERE PCODE='" + hash["PCODE"] + "'";
                
                cmd = new OleDbCommand(sql, oleCon);
                affectedRow = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return affectedRow > 0;
        }

        internal static bool isExistedEvalueInfo(Hashtable hash)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("SELECT COUNT(*) FROM PERSONAL_EVALUATION WHERE PCODE='" + hash["PCODE"] + "'", oleCon);
                if ((int)cmd.ExecuteScalar() > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return b;
        }

        /// <summary>
        /// 승진후보자명부 작성에서 대상자가 교감일 경우 명렬에서 삭제했을 때 모든 관련 정보를 삭제함
        /// </summary>
        /// <param name="pcode">pcode</param>
        /// <returns>true/false</returns>
        internal static bool deleteAllPTargetInfo(string pcode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                string[] tables = { "P_TARGETS", "LIST_TYPE1", "LIST_TYPE2", "LIST_TYPE3", "LIST_TYPE4" };
                string key = "PCODE";
                for (int i = 0; i < tables.Length; i++)
                {
                    if (i > 0) key = "L_PCODE";
                    cmd = new OleDbCommand("DELETE FROM " + tables[i] + " WHERE " + key + "='" + pcode + "'", oleCon);
                    cmd.ExecuteNonQuery();
                }
                b = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }
            return b;
        }

        /// <summary>
        /// 교감인 경우 대상자 등록
        /// </summary>
        /// <param name="hash">Hashtable hash</param>
        /// <returns>true/false</returns>
        internal static bool saveCandidateInfo(Hashtable hash)
        {
            OleDbCommand cmd = null;
            int affectedRow = 0;
            try
            {
                string sql = "INSERT INTO P_TARGETS (PCODE, NAME, SEX) VALUES ('" +
                            hash["PCODE"] + "','" +
                            hash["NAME"] + "','" +
                            hash["SEX"] + "'" +
                            ")";

                cmd = new OleDbCommand(sql, oleCon);
                affectedRow = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return affectedRow > 0;
        }

        /// <summary>
        /// 항목코드로 항목명을 구한다. 
        /// </summary>
        /// <param name="item_code">항목코드</param>
        /// <returns>항목명</returns>
        internal static string getAddItemName(string item_code)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            string name = "";
            try
            {
                cmd = new OleDbCommand("SELECT NAME FROM ADDITION_ITEM WHERE ITEM='" + item_code + "'", oleCon);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    name = reader.GetString(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return name;
        }

        /// <summary>
        /// 가산점 그룹 리스트를 구한다.
        /// </summary>
        /// <returns>{코드, 그룹명}해시 리스트</returns>
        internal static ArrayList getAddGroupList()
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT CODE, NAME, REF_CODE FROM ADDITION_GROUP ORDER BY CODE ASC", oleCon);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Hashtable hash = new Hashtable();
                    hash["CODE"] = reader.GetString(0);
                    hash["NAME"] = reader.GetString(1);
                    hash["REF_CODE"] = reader.IsDBNull(2) ? "" : reader.GetString(2);

                    list.Add(hash);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static ArrayList getAddItemList(string group_code)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            ArrayList list = new ArrayList();
            try
            {
                cmd = new OleDbCommand("SELECT * FROM ADDITION_ITEM WHERE GROUPS='" + group_code + "' ORDER BY ITEM ASC", oleCon);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Hashtable hash = new Hashtable();
                    hash["TYPE"] = reader.GetByte(1);
                    hash["GROUPS"] = reader.GetString(2);
                    hash["ITEM"] = reader.GetString(3);
                    hash["NAME"] = reader.GetString(4);
                    hash["DONATOR"] = reader.IsDBNull(5) ? "" : reader.GetString(5);
                    hash["MONTHLYPOINT"] = reader.IsDBNull(6) ? 0.0 : reader.GetDouble(6);
                    hash["DAILYPOINT"] = reader.IsDBNull(7) ? 0.0 : reader.GetDouble(7);
                    hash["TRAINNINGTIME"] = reader.IsDBNull(8) ? 0 : reader.GetInt16(8);
                    hash["POINTPERTIME"] = reader.IsDBNull(9) ? 0.0 : reader.GetDouble(9);
                    hash["MAXPERYEAR"] = reader.IsDBNull(10) ? 0.0 : reader.GetDouble(10);
                    hash["MAXPOINT"] = reader.IsDBNull(11) ? 0.0 : reader.GetDouble(11);
                    hash["APPLYSTARTDATE"] = reader.IsDBNull(12) ? DateTime.Parse("1900-01-01") : reader.GetDateTime(12);
                    hash["APPLYENDDATE"] = reader.IsDBNull(13) ? DateTime.Parse("1900-01-01") : reader.GetDateTime(13);

                    list.Add(hash);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                reader.Close();
                cmd.Dispose();
            }
            return list;
        }

        internal static bool deleteAddItem(string itemCode)
        {
            OleDbCommand cmd = null;
            bool b = false;
            try
            {
                cmd = new OleDbCommand("DELETE FROM ADDITIONTABLE WHERE ADD_ITEM='" + itemCode + "'", oleCon);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    b = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return b;
        }

        /// <summary>
        /// 가산점 출력 순서 변경
        /// </summary>
        /// <param name="inx">PK</param>
        /// <param name="order">순서</param>
        internal static void additionChangeOrder(int inx, int order)
        {
            OleDbCommand cmd = null;
            try
            {
                //MessageBox.Show("UPDATE ADDITIONTABLE SET ADD_ORDER=" + order + " WHERE INX=" + inx);
                cmd = new OleDbCommand("UPDATE ADDITIONTABLE SET ADD_ORDER=" + order + " WHERE INX=" + inx, oleCon, oleTrans);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 가산점 다음 순서 반환
        /// 순서의 최대값을 찾은 다음 1 더해서 반환함
        /// </summary>
        /// <param name="pcode">개인번호</param>
        /// <returns>다음 순서</returns>
        internal static int addNextOrder(string pcode)
        {
            OleDbCommand cmd = null;
            OleDbDataReader reader = null;
            int nextOrder = 0;
            cmd = new OleDbCommand("SELECT TOP 1 ADD_ORDER FROM ADDITIONTABLE WHERE PCODE='" + pcode + "' ORDER BY ADD_ORDER DESC", oleCon);
            reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                nextOrder = reader.GetInt32(0);
            }

            return nextOrder + 1;
        }
    }
}
